package com.hotelManagerSystem.mapper;

import com.hotelManagerSystem.model.Staff;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.hotelManagerSystem.model.StaffDepartment;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.util.List;

import java.util.List;

import java.util.List;

/**
 * <p>
 *  Mapper 接口
 * </p>
 *
 * @author treasy
 * @since 2021-12-22
 */
public interface StaffMapper extends BaseMapper<Staff> {

    @Select("select * from staff where username=#{username}")
    Staff queryByUsername(String username);

    /**
     * @author treasy
     * @description 根据部门查询未正在打扫的员工的信息
     * @return
     */
    @Select("SELECT\n" +
            "\tstaff.staff_id,\n" +
            "\tstaff.username,\n" +
            "\tstaff.PASSWORD,\n" +
            "\tstaff.salt,\n" +
            "\tstaff.NAME,\n" +
            "\tstaff.sex,\n" +
            "\tstaff.tel,\n" +
            "\tstaff.head_img,\n" +
            "\tstaff.staff_state \n" +
            "FROM\n" +
            "\tstaff\n" +
            "\tINNER JOIN staff_role ON staff.staff_id = staff_role.staff_id\n" +
            "\tINNER JOIN role ON staff_role.role_id = role.role_id \n" +
            "WHERE\n" +
            "\trole.role_name = '清洁工' AND staff.username NOT IN (\n" +
            "SELECT DISTINCT\n" +
            "\tstaff.username\n" +
            "FROM\n" +
            "\tstaff\n" +
            "\tINNER JOIN cleaning_record ON cleaning_record.staff_id = staff.staff_id \n" +
            "WHERE\n" +
            "\tcleaning_record.cleaning_state = 0" +
            ")")
    List<Staff> queryStaffByCleanOrderState();

    //二:黑名单功能---根据职工的id把该用户添加进黑名单
    @Update("update `staff` set staff_state=0 where staff_id=#{staffId}")
    int addStaffById(Integer staffId);
    //三:黑名单功能:根据职工的id把该该用户从黑名单中移除(即黑名单的删除)
    @Update("update `staff` set staff_state=1 where  staff_id=#{staffId}" )
    int deleteStaffById(Integer staffId);
    //四:查询所有黑名单职工
    @Select("select * from `staff` where staff_state=0")
    List<Staff> selectBlackStaffList();
    //五:查询所有白名单职工
    @Select("select * from `staff` where staff_state=1")
    List<Staff> selectWhiteStaffList();
    //六:根据用户名查询职工列表(模糊查询)
    @Select("select * from `staff` where username like concat('%',#{username},'%')")
    List<Staff> selectStaffByUsername(String username);
    //查询员工列表
    @Select("\n" +
            "select s.staff_id,s.username,s.`name`,s.password,s.sex,d.department_name,s.tel  from `staff` s \n" +
            "left JOIN `department_staff` ds\n" +
            "on  s.staff_id = ds.staff_id\n" +
            "left join  `department` d\n" +
            "on d.department_id = ds.department_id")
    List<StaffDepartment> queryStaffDepartmentList();
    //插入语句
    @Insert("insert into `staff` (staff_id,username,name,password,sex,tel) values(null,username,name,password,sex,tel)")
    boolean insertStaff(Staff staff);
    //根据员工的用户名进行查询指定记录
    @Select("select s.staff_id,s.username,s.`name`,s.password,s.sex,d.department_name,s.tel  from `staff` s \n" +
            "left JOIN `department_staff` ds\n" +
            "on  s.staff_id = ds.staff_id\n" +
            "left join  `department` d\n" +
            "on d.department_id = ds.department_id\n" +
            "where s.username like concat('%',#{username},'%')\n")
    List<StaffDepartment> queryStaffByUsername(String username);


    @Select("select s.staff_id,s.username,s.`name`,s.password,s.sex,d.department_name,s.tel  from `staff` s \n" +
            "left JOIN `department_staff` ds\n" +
            "on  s.staff_id = ds.staff_id\n" +
            "left join  `department` d\n" +
            "on d.department_id = ds.department_id\n" +
            "where s.staff_id=#{staffId} ")
    StaffDepartment selectOneStaff(Integer staffId);



    @Update("update `staff` set name=#{name},password=#{password},sex=#{sex},tel=#{tel} where staff_id=#{staffId}")
    boolean updateStaff(Staff staff);
}
